package model;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Observable;
import java.util.Vector;

import view.SplashScreen;

public class Database extends Observable
{

	public static Vector<Account> allAcc = null; //replico tutti i contatti del DB in locale
	public static Account Utente = null;//account temporaneo durante la registrazione ed effettivo dopo il login
	public static Account tempUtente = null;//account temporaneo per la modifica
	public static Evento Evento = null;
	public static Evento tempEvento = null;
	// JDBC driver name and database URL
	private static String JDBC_DRIVER = "com.mysql.jdbc.Driver";  //driver: traduttore della comunicazione con un db particolare (java si adatta ai diversi database)
	//private static String DB_URL = "jdbc:mysql://87.20.14.71:3306/load";  //posizione del db
	private static String DB_URL = "jdbc:mysql://load14.no-ip.biz:3306/load";  //posizione del db
	//  Database credentials
	public static String USER = "load14";  //credenziali per effettuare l'accesso al database
	private static String PASS = "gabbibbo";
	/* bRVCJeH7qXjbhzVS lorenzo
	 * P93Gfrs4yZq4N6R7 danilo
	 * h5svwJRwsrCRHTVd psyco
	 */
	private static Connection conn = null;
	private static Statement stmt  = null;

	//costruttore del model, apre la connessione con il database
	public Database(){
		connectDB();
	}

	public void uppdater(){
		setChanged();
		notifyObservers();
	}

	public static void connectDB(){
		try{
			System.out.println("Provo a connettermi a: "+DB_URL);
			Class.forName(JDBC_DRIVER);
			conn = DriverManager.getConnection(DB_URL,USER,PASS);
			stmt = conn.createStatement();
			System.out.println("DB connesso");
		}catch(SQLException se){
			//System.out.println(DB_URL+" - database non trovato");
			//System.out.println("Creo la connessione con il dtabase locale");
			try {
				DB_URL="jdbc:mysql://localhost/load";
				USER="root";
				PASS="";
				Class.forName(JDBC_DRIVER);
				DriverManager.setLoginTimeout(5);
				conn = DriverManager.getConnection(DB_URL,USER,PASS);
				stmt = conn.createStatement();
			} catch (ClassNotFoundException e) {
				System.out.println("connessione non disponibile");
				//e.printStackTrace();
			} catch (SQLException e) {
				System.out.println("database non disponibile");
				//e.printStackTrace();
			}
			se.printStackTrace();
		}catch(ClassNotFoundException e){
			//System.out.println("connessione non disponibile");
			e.printStackTrace();
		}
	}

	//chiude la connessione e lo statement
	public static void closeDB(){
		try {
			conn.close();
			stmt.close();
			System.out.println("DB disconnesso");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			System.exit(0);
		}
	}

	//controllo account
	public static boolean validateLogin(String username, String password){
		ResultSet result=null;
		boolean flag=false;
		try {
			result = Database.getQueryResult("SELECT acc_id FROM account WHERE username = '"+username+"' AND password = '"+password+"'");
			result.next();
			if (result.isLast()){
				setAccount(result.getInt("acc_id"));
				flag = true;
			} else
				System.out.println("Errore");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

	public static void nuovoEvento(){
		Evento = new Evento();
	}
	public static void eventoTemp(int id){
		ResultSet result=null;
		try {
			result = Database.getQueryResult("SELECT * FROM evento WHERE evn_id = '"+id+"'");
			result.next();
			tempEvento = new Evento(result.getInt("evn_id"),
					result.getString("nome"),
					result.getString("descrizione"),
					result.getString("luogo"),
					result.getString("day"),
					result.getString("time"),
					result.getString("type"),
					result.getInt("level"),
					Database.Utente.getID());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void accountUtenteTemp(){
		Utente = new Account();
	}
	public static void accountTemp(){
		ResultSet result=null;
		try {
			result = Database.getQueryResult("SELECT * FROM account WHERE acc_id = '"+Utente.getID()+"'");
			result.next();
			tempUtente = new Account(result.getInt("acc_id"),
					result.getString("photo"),
					result.getString("username"),
					result.getString("password"),
					result.getString("f_name"),
					result.getString("s_name"),
					result.getInt("sex"),
					result.getInt("level"),
					Email.accEml(Utente.getID()),
					Indirizzo.accAdr(Utente.getID()),
					Telefono.accTel(Utente.getID()),
					accRub(Utente.getID()));
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static boolean validateRegistration(String username){
		ResultSet result=null;
		boolean flag=true;
		try {
			result = Database.getQueryResult("SELECT username FROM account WHERE username = '"+username+"'");
			result.next();
			if (result.isLast())
				flag = false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}

	public static void setAccount(int acc_id){
		ResultSet result=null;
		try {
			result = Database.getQueryResult("SELECT * FROM account WHERE acc_id = '"+acc_id+"'");
			result.next();
			Utente = new Account(result.getInt("acc_id"),
					result.getString("photo"),
					result.getString("username"),
					result.getString("password"),
					result.getString("f_name"),
					result.getString("s_name"),
					result.getInt("sex"),
					result.getInt("level"),
					Email.accEml(acc_id),
					Indirizzo.accAdr(acc_id),
					Telefono.accTel(acc_id),
					accRub(acc_id));
			if (Utente.getLevel()==512)
				Utente.setRubrica(allAcc);

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void loadAllContacts(){
		allAcc = new Vector<Account>();
		try {
			int opz = 0;
			ResultSet cont = Database.getQueryResult("SELECT COUNT(acc_id) FROM account");
			cont.last();
			opz=cont.getInt(1);
			if (opz==0){
				SplashScreen.setContactCount(1);
				SplashScreen.setContactProgress();
			}else
				SplashScreen.setContactCount(opz);
			ResultSet result = Database.getQueryResult("SELECT * FROM account");
			int id;
			while (result.next()){
				Account a = new Account(result.getInt("acc_id"),
						result.getString("photo"),
						result.getString("f_name"),
						result.getString("s_name"),
						result.getInt("sex"));
				allAcc.add(a);
				SplashScreen.setContactProgress();
			}
			for (int i=0;i<allAcc.size();i++){
				id = allAcc.get(i).getID();
				allAcc.get(i).setEmail(Email.accEml(id));
				allAcc.get(i).setIndirizzo(Indirizzo.accAdr(id));
				allAcc.get(i).setTelefono(Telefono.accTel(id));
			}

		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static Vector<Account> evnInv(int evn_id) {
		Vector<Account> inv = new Vector<Account>();
		Vector<Integer> contact_id = new Vector<Integer>();
		try {
			ResultSet result = Database.getQueryResult("SELECT * FROM invitati WHERE evn_id="+evn_id);
			while (result.next())
				contact_id.add(result.getInt("inv_id"));
			result.close();
			for (int i = 0; i<contact_id.size(); i++){
				result = Database.getQueryResult("SELECT * FROM account WHERE acc_id="+contact_id.get(i));
				result.next();
				Account a = new Account(result.getInt("acc_id"),result.getString("photo"),
						result.getString("f_name"),result.getString("s_name"),result.getInt("sex"));
				a.setEmail(Email.accEml(contact_id.get(i)));
				a.setIndirizzo(Indirizzo.accAdr(contact_id.get(i)));
				a.setTelefono(Telefono.accTel(contact_id.get(i)));
				inv.add(a);
				result.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return inv;
	}

	public static Vector<Account> accRub(int acc_id){
		Vector<Account> agn = new Vector<Account>();

		Vector<Integer> contact_id = new Vector<Integer>();
		try {
			ResultSet result = Database.getQueryResult("SELECT * FROM rubrica WHERE acc_id="+acc_id);
			while (result.next())
				contact_id.add(result.getInt("cnt_id"));
			result.close();
			for (int i = 0; i<contact_id.size(); i++){
				result = Database.getQueryResult("SELECT * FROM account WHERE acc_id="+contact_id.get(i));
				result.next();
				Account a = new Account(result.getInt("acc_id"),result.getString("photo"),
						result.getString("f_name"),result.getString("s_name"),result.getInt("sex"));
				a.setEmail(Email.accEml(contact_id.get(i)));
				a.setIndirizzo(Indirizzo.accAdr(contact_id.get(i)));
				a.setTelefono(Telefono.accTel(contact_id.get(i)));
				agn.add(a);


				result.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return agn;
	}

	public static void insertAccountOnDB() {
		Utente.insertAccountOnDb();
	}

	public static void updateAccountOnDB() {
		Utente.UpdateAccountOnDb();
	}

	//esegue una query di selezione dal db (SELECT * FROM * WHERE *)

	public static ResultSet getQueryResult(String query) {
		ResultSet result = null;
		//System.out.println(query);
		try {
			result = stmt.executeQuery(query);
			stmt.clearBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	//esegue una query di inserimento nel db (INSERT INTO)
	public static void setResource(String query){
		//System.out.println(query);
		try {
			stmt.execute(query);
			stmt.clearBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	//esegue una query di aggiornamento del db (UPDATE)
	public static void updateResource(String query){
		//System.out.println(query);
		try {
			stmt.executeUpdate(query);
			stmt.clearBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	//esegue una query di eliminazione dal db (DELETE)
	public static void deleteResource(String query){
		//System.out.println(query);
		try {
			stmt.execute(query);
			stmt.clearBatch();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
